<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Binding parameters &amp; columns</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, Perl, DBI, binding parameters, binding columns, 
tutorial, database, programming, development">
<meta name="description" content="In this chapter of the MySQL Perl tutorial, we 
bind parameters &amp; columns.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Binding parameters &amp; columns</h1>

<p>
SQL statements are often dynamically built. A user provides some input and
it is built into the statement. A programmer must be cautious every time he 
deals with an input from a user. It has some serious security implications. 
The recommended way to dynamically build SQL statements is to use parameter binding. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Binding parameters guards the program against SQL injections. It automatically escapes
some special characters and allows them to be handled correctly. Many databases
also increase significantly their performace, when we prepare the statements and
bind the parameters. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $name = "Volkswagen"; 

my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");
$sth->execute($name);

my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
The example selects a row from the Cars table for a specific car name. 
</p>

<pre class="explanation">
my $name = "Volkswagen"; 
</pre>

<p>
This is a value that could come from a user. For example from a HTML form.
</p>

<pre class="explanation">
my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");
</pre>

<p>
The question mark (?) is a placeholder for a value. It is added later in the
script. 
</p>

<pre class="explanation">
$sth->execute($name);
</pre>

<p>
In the <code>execute()</code> method we bind the value to the placeholder. 
</p>

<hr class="btm">

<p>
The following example is the same as the previous one; this time we
use the <code>bind_param()</code> method. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $name = "Volkswagen"; 

my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");
$sth->bind_param(1, $name);
$sth->execute();

my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
Retrieving a row with a parameter binding, using the <code>bind_param()</code>
method. 
</p>

<pre class="explanation">
$sth->bind_param(1, $name);
</pre>

<p>
The <code>bind_param()</code> method takes a value and associates it with the 
placeholder inside the SQL statement. There can be more placeholders. The placeholders
are numbered from 1. 
</p>


<h2>Quoting parameters</h2>

<p>
Using placeholders and binding parameters to them is the best way to deal
with dynamic SQL statement building. Sometimes placeholders cannot be used. 
For example when we want to dynamically choose a table name. In such
cases, we can concatenate the SQL string and use the <code>quote()</code>
and <code>quote_identifier()</code> methods. We must use these methods
for the variables, otherwise we introduce serious security bugs.
</p>

<p>
The <code>quote()</code> method quotes a string literal for use as a literal 
value in an SQL statement. It escapes any special characters (such as quotation marks) 
contained within the string and adds the required type of outer quotation marks.
The <code>quote_identifier()</code> method quotes an identifier (table name etc.) 
for use in an SQL statement. It escapes any special characters 
(such as double quotation marks) it contains and adds the required type of 
outer quotation marks.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $table = "Cars";
my $name = "Volkswagen";

my $sql = sprintf "SELECT * FROM %s WHERE Name = %s", 
    $dbh->quote_identifier($table), $dbh->quote($name);

my $sth = $dbh->prepare($sql);
$sth->execute();

my @row;
while (@row = $sth->fetchrow_array()) {
    print "@row\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example we build the SQL statement string dynamically
using the <code>quote()</code> and <code>quote_identifier()</code> methods.
</p>

<pre class="explanation">
my $table = "Cars";
my $name = "Volkswagen";
</pre>

<p>
These are the Perl scalars to be used in the SQL statement. 
</p>

<pre class="explanation">
my $sql = sprintf "SELECT * FROM %s WHERE Name = %s", 
    $dbh->quote_identifier($table), $dbh->quote($name);
</pre>

<p>
We have a more complex SQL statement. It is not possible to build this
statement using placeholders. We use the quote methods to quote the
supplied scalars. 
</p>


<h2>Binding columns</h2>

<p>
When using the fetch methods, we copy the returned values to
Perl variables. This process may be simplified and made faster
by binding columns. The Perl DBI has <code>bind_col()</code> and
<code>bind_columns()</code> methods, which associate Perl variables 
with table columns. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 4" );  
$sth->execute();

$sth->bind_columns(\my($id, $name, $price));
      
while ($sth->fetchrow_arrayref()) {
    print "$id $name $price\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example, we bind three columns of the Cars table
to the $id, $name and $price variables. 
</p>

<pre class="explanation">
$sth->bind_columns(\my($id, $name, $price));
</pre>

<p>
We bind the variables to the colums of the Cars table
with the <code>bind_columns()</code> method.
</p>

<pre class="explanation">
while ($sth->fetchrow_arrayref()) {
    print "$id $name $price\n";
}
</pre>

<p>
We traverse the returned data and print the values
to the console. 
</p>

<p>
In this part of the MySQL Perl tutorial we talked about binding parameters.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 7, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

